
// Author:					HungNM
// Created:					16-08-2012
// Last Modified:			16-08-2012
//

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using eProject.WebHelper;
using eProject.Data.MSSQL;
	
namespace eProject.Data
{
	
	public static class DBUsers
    {
        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <returns></returns>
        private static string GetConnectionString()
        {
            return ConnectionHelper.GetConnectionString();

        }


		/// <summary>
		/// Inserts a row in the Users table. Returns new integer id.
		/// </summary>
		/// <param name="userName"> userName </param>
		/// <param name="password"> password </param>
		/// <param name="challengeCode"> challengeCode </param>
		/// <param name="email"> email </param>
		/// <param name="roles"> roles </param>
        /// <param name="firstName"> firstName </param>
        /// <param name="lastName"> lastName </param>
		/// <param name="dateOfBirth"> dateOfBirth </param>
		/// <param name="gender"> gender </param>
		/// <param name="address"> address </param>
		/// <param name="phoneNumber"> phoneNumber </param>
		/// <param name="distance"> distance </param>
		/// <param name="isApproved"> isApproved </param>
		/// <param name="isBlocked"> isBlocked </param>
		/// <param name="isDeleted"> isDeleted </param>
		/// <param name="activationCode"> activationCode </param>
		/// <param name="activationTime"> activationTime </param>
		/// <param name="createdDate"> createdDate </param>
		/// <param name="lastUpdate"> lastUpdate </param>
		/// <returns>int</returns>
		public static int Create(
			string userName, 
			string password, 
			string challengeCode, 
			string email, 
			int roles, 
			string firstName,
            string lastName, 
			DateTime dateOfBirth, 
			bool gender, 
			string address, 
			string phoneNumber, 
			long distance, 
			bool isApproved, 
			bool isBlocked, 
			bool isDeleted, 
			Guid activationCode, 
			DateTime activationTime, 
			DateTime createdDate, 
			DateTime lastUpdate) 
		{
			int newID = -1;
			try
			{
				using (SqlHelper db = new SqlHelper(GetConnectionString()))
				{
					newID = Convert.ToInt32(db.ExecScalarProc("mp_Users_Insert",
                        "@UserName", userName,
                        "@Password", password,
                        "@ChallengeCode", challengeCode,
                        "@Email", email,
                        "@Roles", roles,
                        "@FirstName", firstName,
                        "@LastName", lastName,
                        "@DateOfBirth", dateOfBirth,
                        "@Gender", gender,
                        "@Address", address,
                        "@PhoneNumber", phoneNumber,
                        "@Distance", distance,
                        "@IsApproved", isApproved,
                        "@IsBlocked", isBlocked,
                        "@IsDeleted", isDeleted,
                        "@ActivationCode", activationCode,
                        "@ActivationTime", activationTime,
                        "@CreatedDate", createdDate,
                        "@LastUpdate", lastUpdate
					));
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return newID;
		}


		/// <summary>
		/// Updates a row in the Users table. Returns true if row updated.
		/// </summary>
		/// <param name="id"> id </param>
		/// <param name="userName"> userName </param>
		/// <param name="password"> password </param>
		/// <param name="challengeCode"> challengeCode </param>
		/// <param name="email"> email </param>
		/// <param name="roles"> roles </param>
        /// <param name="firstName"> firstName </param>
        /// <param name="lastName"> lastName </param>
		/// <param name="dateOfBirth"> dateOfBirth </param>
		/// <param name="gender"> gender </param>
		/// <param name="address"> address </param>
		/// <param name="phoneNumber"> phoneNumber </param>
		/// <param name="distance"> distance </param>
		/// <param name="isApproved"> isApproved </param>
		/// <param name="isBlocked"> isBlocked </param>
		/// <param name="isDeleted"> isDeleted </param>
		/// <param name="activationCode"> activationCode </param>
		/// <param name="activationTime"> activationTime </param>
		/// <param name="createdDate"> createdDate </param>
		/// <param name="lastUpdate"> lastUpdate </param>
		/// <returns>bool</returns>
		public static bool Update(
			long  id, 
			string userName, 
			string password, 
			string challengeCode, 
			string email, 
			int roles,
            string firstName,
            string lastName, 
			DateTime dateOfBirth, 
			bool gender, 
			string address, 
			string phoneNumber, 
			long distance, 
			bool isApproved, 
			bool isBlocked, 
			bool isDeleted, 
			Guid activationCode, 
			DateTime activationTime, 
			DateTime createdDate, 
			DateTime lastUpdate) 
		{
			int rowsAffected = -1;
			try
			{
				using (SqlHelper db = new SqlHelper(GetConnectionString()))
				{
					rowsAffected = db.ExecNonQueryProc("mp_Users_Update",
						"@Id", id,
						"@UserName", userName,
						"@Password", password,
						"@ChallengeCode", challengeCode,
						"@Email", email,
						"@Roles", roles,
                        "@FirstName", firstName,
                        "@LastName", lastName,
						"@DateOfBirth", dateOfBirth,
						"@Gender", gender,
						"@Address", address,
						"@PhoneNumber", phoneNumber,
						"@Distance", distance,
						"@IsApproved", isApproved,
						"@IsBlocked", isBlocked,
						"@IsDeleted", isDeleted,
						"@ActivationCode", activationCode,
						"@ActivationTime", activationTime,
						"@CreatedDate", createdDate,
						"@LastUpdate", lastUpdate
					);
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return (rowsAffected > 0);
			
		}
		
		/// <summary>
		/// Deletes a row from the Users table. Returns true if row deleted.
		/// </summary>
		/// <param name="id"> id </param>
		/// <returns>bool</returns>
		public static bool Delete(long id, bool isDeleted) 
		{
			int rowsAffected = -1;
			try
			{
				using (SqlHelper db = new SqlHelper(GetConnectionString()))
				{
					rowsAffected = Convert.ToInt32(db.ExecNonQueryProc("mp_Users_Delete",
						"@Id", id,
                        "@IsDeleted", isDeleted
					));
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return (rowsAffected > 0);
			
		}
		
		/// <summary>
		/// Gets an IDataReader with one row from the Users table.
		/// </summary>
		/// <param name="id"> id </param>
		public static IDataReader GetOne(long  id)  
		{
			SqlDataReader reader = null;
			try
			{
				SqlHelper db = new SqlHelper(GetConnectionString());				
				reader = db.ExecDataReaderProc("mp_Users_SelectOne",
					"@Id", id
				);
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return reader;
		}

        /// <summary>
        /// Gets an IDataReader with one row from the Users table.
        /// </summary>
        /// <param name="id"> id </param>
        public static bool CheckUser(string userName)
        {
            bool isValid = false;
            try
            {
                SqlHelper db = new SqlHelper(GetConnectionString());
                SqlDataReader reader = db.ExecDataReaderProc("mp_Users_CheckUser",
                    "@UserName", userName
                );

                if (reader.HasRows)
                    isValid = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return isValid;
        }

        /// <summary>
        /// Gets an IDataReader with one row from the Users table.
        /// </summary>
        /// <param name="id"> id </param>
        public static IDataReader CheckForLogin(string userName)
        {
            SqlDataReader reader = null;
            try
            {
                SqlHelper db = new SqlHelper(GetConnectionString());
                reader = db.ExecDataReaderProc("mp_Users_CheckForLogin",
                    "@UserName", userName
                );
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return reader;
        }

		/// <summary>
		/// Gets a count of rows in the Users table.
		/// </summary>
		public static int GetCount()
		{
			int count = 0;
			try
			{
				using (SqlHelper db = new SqlHelper(GetConnectionString()))
				{
					count = Convert.ToInt32(db.ExecScalarProc("mp_Users_GetCount"));
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return count;	
		}
		
		/// <summary>
		/// Gets an IDataReader with all rows in the Users table.
		/// </summary>
		public static IDataReader GetAll()
		{
			SqlDataReader reader = null;
			try
			{
				SqlHelper db = new SqlHelper(GetConnectionString());				
				reader = db.ExecDataReaderProc("mp_Users_SelectAll");
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return reader;
		}


        /// <summary>
        /// Gets an IDataReader with all rows in the Users table.
        /// </summary>
        public static IDataReader GetAllWithSearchText(string searchText, bool isDeleted)
        {
            SqlDataReader reader = null;
            try
            {
                SqlHelper db = new SqlHelper(GetConnectionString());
                reader = db.ExecDataReaderProc("mp_Users_SelectAllWithSearchText",
                    "@SearchText", searchText,
                    "@IsDeleted", isDeleted);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return reader;
        }


		/// <summary>
		/// Gets a page of data from the Users table.
		/// </summary>
		/// <param name="pageNumber">The page number.</param>
		/// <param name="pageSize">Size of the page.</param>
		/// <param name="totalPages">total pages</param>
		public static IDataReader GetPage(
			int pageNumber, 
			int pageSize,
			out int totalPages)
		{
			totalPages = 1;
			int totalRows
				= GetCount();
				
			if (pageSize > 0) totalPages = totalRows / pageSize;
		
			if (totalRows <= pageSize)
			{
				totalPages = 1;
			}
			else
			{
				int remainder;
				Math.DivRem(totalRows, pageSize, out remainder);
				if (remainder > 0)
				{
					totalPages += 1;
				}
			}
			
			SqlDataReader reader = null;
			try
			{
				SqlHelper db = new SqlHelper(GetConnectionString());				
				reader = db.ExecDataReaderProc("mp_Users_SelectPage",
					"@PageNumber", pageNumber,
					"@PageSize", pageSize
				);
			}
			catch(Exception ex)
			{
				throw ex;
			}
			
			return reader;
		}
	
	}

}


